﻿Imports System.Data


Public Class Form1
    Public run_path As String = ""
    Public data_path As String = ""
    Public data_cnn As New SqlServerCe.SqlCeConnection
    Public msg As String
    Public input_yymm As String
    Public msg_stack As New System.Collections.Generic.Stack(Of String)
    Public sql_path As String = ""
    Public xls_path As String = ""

    Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        data_cnn.Close()
    End Sub
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim dt As DateTime = Now
        DateTimePicker1.Value = DateAdd(DateInterval.Month, -1, New DateTime(dt.Year, dt.Month, 1))


        run_path = System.Threading.Thread.GetDomain().BaseDirectory
#If DEBUG Then
        'run_path = "D:\learn\excel\新建文件夹\"
        Dim test_path As String = "D:\learn\dev\donet\sqlce"
        data_path = test_path
        sql_path = test_path + "\sql"
        xls_path = test_path
#Else
        'MsgBox("aaa")
        data_path = My.Settings.data_path.Replace("|exepath|", run_path).Replace("\\", "\")
        sql_path = My.Settings.sql_path.Replace("|exepath|", run_path).Replace("\\", "\")
        xls_path = My.Settings.xls_path.Replace("|exepath|", run_path).Replace("\\", "\")
#End If

        'Dim conncfg As Configuration.ConnectionStringSettings = Configuration.ConfigurationManager.ConnectionStrings("expxls.My.MySettings.dataConnectionString")

        Dim sqlceconn_build As New SqlServerCe.SqlCeConnectionStringBuilder
        'sqlceconn_build.ConnectionString = conncfg.ConnectionString
        sqlceconn_build.DataSource = IO.Path.Combine(data_path, "db.sdf")
        sqlceconn_build.DefaultLockEscalation = 1000
        sqlceconn_build.MaxBufferSize = 262140
        sqlceconn_build.MaxDatabaseSize = 4091

        data_cnn.ConnectionString = sqlceconn_build.ConnectionString
        Debug.Print(sqlceconn_build.ConnectionString)
        data_cnn.Open()



        'RichTextBox1.HideSelection = False

        'DTP1.Value = Now().AddDays(-1)
        'DTP2.Value = Now().AddDays(-1)
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        input_yymm = DateTimePicker1.Value.ToString("yyyyMM")
        Dim 表1_fi As New IO.FileInfo(IO.Path.Combine(xls_path, My.Settings.表1_name + input_yymm + ".xls"))
        Dim 表2_fi As New IO.FileInfo(IO.Path.Combine(xls_path, My.Settings.表2_name + input_yymm + ".xls"))

        If 表1_fi.Exists = False Then
            MsgBox(表1_fi.FullName + " 未找到")
            Exit Sub
        End If

        If 表1_fi.Exists = False Then
            MsgBox(表2_fi.FullName + " 未找到")
            Exit Sub
        End If

        Dim wb As New NPOI.HSSF.UserModel.HSSFWorkbook
        Try
            wb = New NPOI.HSSF.UserModel.HSSFWorkbook(New System.IO.FileStream(表1_fi.FullName, IO.FileMode.Open, IO.FileAccess.Read))
        Catch ex As Exception
            MsgBox("读取" + 表1_fi.FullName + "失败:" + ex.Message)
            Exit Sub
        End Try

        Dim sqlcmd As New SqlServerCe.SqlCeCommand
        sqlcmd.Connection = data_cnn


        CXls.sqlceScript(IO.File.ReadAllText(IO.Path.Combine(sql_path, "清理数据.sql"), System.Text.Encoding.Default), data_cnn)

        sqlcmd.CommandText = "delete from 科目余额表"
        sqlcmd.ExecuteNonQuery()
        sqlcmd.CommandText = "delete from 科目辅助余额表"
        sqlcmd.ExecuteNonQuery()
        sqlcmd.CommandText = "delete from 部门辅助余额表"
        sqlcmd.ExecuteNonQuery()


        '启动数据库事务
        Dim transaction As SqlServerCe.SqlCeTransaction
        transaction = data_cnn.BeginTransaction(IsolationLevel.ReadCommitted)
        Dim aaa As String = ""

        For i = 0 To wb.NumberOfSheets - 1
            Dim ws As NPOI.HSSF.UserModel.HSSFSheet = wb.GetSheetAt(i)
            '判断当前表类型
            Dim str As String = ws.GetRow(2).GetCell(1).StringCellValue
            Dim str1 As String = ws.GetRow(3).GetCell(1).StringCellValue

            If str = "科目余额表" Then
                If get_科目余额表(ws) = False Then
                    send_msg(msg_stack)
                    Exit Sub
                End If
            ElseIf str = "辅助余额表" And str1.IndexOf("科  目：") > 0 Then
                If get_科目辅助余额表(ws) = False Then
                    send_msg(msg_stack)
                    Exit Sub
                End If
            End If
        Next


        '处理表二
        Try
            wb = New NPOI.HSSF.UserModel.HSSFWorkbook(New System.IO.FileStream(表2_fi.FullName, IO.FileMode.Open, IO.FileAccess.Read))
        Catch ex As Exception
            MsgBox("读取" + 表2_fi.FullName + "失败:" + ex.Message)
            Exit Sub
        End Try

        For i = 0 To wb.NumberOfSheets - 1
            Dim ws As NPOI.HSSF.UserModel.HSSFSheet = wb.GetSheetAt(i)
            '判断当前表类型
            Dim str As String = ws.GetRow(2).GetCell(0).StringCellValue
            Dim str1 As String = ws.GetRow(4).GetCell(1).StringCellValue

            If str = "辅助余额表" And str1.IndexOf("部门档案：") > 0 Then
                If get_部门辅助余额表(ws) = False Then
                    send_msg(msg_stack)
                    Exit Sub
                End If
            End If
        Next

        '处理数据
        Dim sql As String
        Dim ds As DataSet

        sql = IO.File.ReadAllText(IO.Path.Combine(sql_path, "管理费_dataproc.sql"), System.Text.Encoding.Default)
        ds = CXls.sqlceScript(sql, data_cnn)
        sql = IO.File.ReadAllText(IO.Path.Combine(sql_path, "收入表_dataproc_成本.sql"), System.Text.Encoding.Default)
        ds = CXls.sqlceScript(sql, data_cnn)
        sql = IO.File.ReadAllText(IO.Path.Combine(sql_path, "收入表_dataproc_收入.sql"), System.Text.Encoding.Default)
        ds = CXls.sqlceScript(sql, data_cnn)

        transaction.Commit()



        MsgBox("处理完成")

    End Sub
    Function get_部门辅助余额表(sht As NPOI.HSSF.UserModel.HSSFSheet) As Boolean
        msg_stack.Push(sht.SheetName)
        Dim offset As Integer = 1
        Dim last_bmbm As String = ""
        While 1 = 1

            If sht.LastRowNum < (offset + 1) OrElse sht.GetRow(offset + 1).GetCell(0).StringCellValue <> "辅助余额表" Then
                '找不到下一个子表了，退出
                Exit While
            End If

            Dim dt As String = sht.GetRow(offset + 2).GetCell(0).StringCellValue
            If process_dt(dt, input_yymm) = False Then
                Return False
            End If
            
            Dim bm As String = sht.GetRow(offset + 3).GetCell(1).StringCellValue
            Dim m As System.Text.RegularExpressions.Match = System.Text.RegularExpressions.Regex.Match(bm, "【部门档案：(\d+)\/(.+)】")
            If m.Success = False Then
                msg_stack.Push(msg_stack.Pop + vbCrLf + (sht.SheetName + "   部门档案匹配失败:" + bm))
                Return False
            End If
            Dim bmbm As String = m.Groups(1).Value
            Dim bmmc As String = m.Groups(2).Value

            Dim sqlcmd As New SqlServerCe.SqlCeCommand
            sqlcmd.Connection = data_cnn
            If last_bmbm <> bmbm Then
                sqlcmd.CommandText = "delete from 部门辅助余额表 where 部门编码='" + bmbm + "' and 数据日期='" + dt + "'"
                'sqlcmd.ExecuteNonQuery()
            End If
            'transaction.Commit()
            'transaction = SQLCEconn.BeginTransaction(IsolationLevel.ReadCommitted)
            sqlcmd.CommandText = "INSERT INTO [部门辅助余额表] ( [主体帐簿名称],[部门编码], [部门档案名称],[摘要], [科目编码],[科目名称], [期初余额方向], [期初余额], [本期借方], [本期贷方], [借方累计], [贷方累计], [期末余额方向], [期末余额],[数据日期],[导入日期]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12,@p13,@p14,@p15,@p16)"

            sqlcmd.Parameters.Clear()
            sqlcmd.Parameters.Add("p1", SqlDbType.NVarChar, 100, "[主体帐簿名称]")
            sqlcmd.Parameters.Add("p2", SqlDbType.NVarChar, 100, "[部门编码]")
            sqlcmd.Parameters.Add("p3", SqlDbType.NVarChar, 200, "[部门档案名称]")
            sqlcmd.Parameters.Add("p4", SqlDbType.NVarChar, 100, "[摘要]")
            sqlcmd.Parameters.Add("p5", SqlDbType.NVarChar, 100, "[科目编码]")
            sqlcmd.Parameters.Add("p6", SqlDbType.NVarChar, 100, "[科目名称]")
            sqlcmd.Parameters.Add("p7", SqlDbType.NVarChar, 100, "[期初余额方向]")
            sqlcmd.Parameters.Add("p8", SqlDbType.Decimal, 20, "[期初余额]")
            sqlcmd.Parameters.Add("p9", SqlDbType.Decimal, 20, "[本期借方]")
            sqlcmd.Parameters.Add("p10", SqlDbType.Decimal, 20, "[本期贷方]")
            sqlcmd.Parameters.Add("p11", SqlDbType.Decimal, 20, "[借方累计]")
            sqlcmd.Parameters.Add("p12", SqlDbType.Decimal, 20, "[贷方累计]")
            sqlcmd.Parameters.Add("p13", SqlDbType.NVarChar, 100, "[期末余额方向]")
            sqlcmd.Parameters.Add("p14", SqlDbType.Decimal, 20, "[期末余额]")
            sqlcmd.Parameters.Add("p15", SqlDbType.NVarChar, 100, "[数据日期]")
            sqlcmd.Parameters.Add("p16", SqlDbType.NVarChar, 100, "[导入日期]")

            offset += 6

            For i = offset To sht.LastRowNum
                Dim row As NPOI.HSSF.UserModel.HSSFRow = sht.GetRow(i)

                If row.Cells(0).StringCellValue = "核算单位：" Then
                    last_bmbm = bmbm
                    Exit For
                End If
                sqlcmd.Parameters(0).Value = sht.SheetName
                sqlcmd.Parameters(1).Value = bmbm
                sqlcmd.Parameters(2).Value = bmmc
                sqlcmd.Parameters(3).Value = row.Cells(0).StringCellValue

                If row.Cells(0).StringCellValue = "总计" Then
                    sqlcmd.Parameters(4).Value = ""
                    sqlcmd.Parameters(5).Value = ""
                Else
                    Dim match As System.Text.RegularExpressions.Match = System.Text.RegularExpressions.Regex.Match(row.Cells(1).StringCellValue, "^((\d+)\\.+)$")
                    If match.Success = False Then
                        msg_stack.Push(msg_stack.Pop + vbCrLf + sht.SheetName + "  " + bmmc + " 科目匹配失败:" + row.Cells(1).StringCellValue)
                        Return False
                    End If
                    sqlcmd.Parameters(4).Value = match.Groups(2).Value
                    sqlcmd.Parameters(5).Value = match.Groups(1).Value
                End If

                sqlcmd.Parameters(6).Value = row.Cells(2).StringCellValue
                sqlcmd.Parameters(7).Value = convert_num(row.Cells(3))
                sqlcmd.Parameters(8).Value = convert_num(row.Cells(4))
                sqlcmd.Parameters(9).Value = convert_num(row.Cells(5))
                sqlcmd.Parameters(10).Value = convert_num(row.Cells(6))
                sqlcmd.Parameters(11).Value = convert_num(row.Cells(7))
                sqlcmd.Parameters(12).Value = row.Cells(8).StringCellValue
                sqlcmd.Parameters(13).Value = convert_num(row.Cells(9))
                sqlcmd.Parameters(14).Value = dt
                sqlcmd.Parameters(15).Value = ""
                sqlcmd.ExecuteNonQuery()

                offset += 1


            Next
            offset += 2

        End While


        msg_stack.Pop()
        Return True
    End Function


    Function get_科目辅助余额表(sht As NPOI.HSSF.UserModel.HSSFSheet) As Boolean
        msg_stack.Push(sht.SheetName)

        Dim dt As String = sht.GetRow(4).GetCell(1).StringCellValue
        Dim km As String = sht.GetRow(3).GetCell(1).StringCellValue
        Dim m As System.Text.RegularExpressions.Match = System.Text.RegularExpressions.Regex.Match(km, "【科  目：((\d+)\/.+)】")
        If m.Success = False Then
            msg_stack.Push(msg_stack.Pop + vbCrLf + sht.SheetName + "   科目匹配失败:" + km)
            Return False
        End If
        Dim kmbm As String = m.Groups(2).Value
        Dim kmmc As String = m.Groups(1).Value

        Dim sqlcmd As New SqlServerCe.SqlCeCommand
        sqlcmd.Connection = data_cnn

        sqlcmd.CommandText = "delete from 科目辅助余额表 where 科目编码='" + kmbm + "' and 数据日期='" + dt + "'"
        'sqlcmd.ExecuteNonQuery()
        'transaction.Commit()
        'transaction = SQLCEconn.BeginTransaction(IsolationLevel.ReadCommitted)
        sqlcmd.CommandText = "INSERT INTO [科目辅助余额表] ( [科目编码], [科目名称],[摘要], [主体帐簿名称],[部门档案名称],[收支类型辅助核算名称], [期初余额方向], [期初余额], [本期借方], [本期贷方], [借方累计], [贷方累计], [期末余额方向], [期末余额],[数据日期],[导入日期]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12,@p13,@p14,@p15,@p16)"

        sqlcmd.Parameters.Clear()
        sqlcmd.Parameters.Add("p1", SqlDbType.NVarChar, 100, "[科目编码]")
        sqlcmd.Parameters.Add("p2", SqlDbType.NVarChar, 200, "[科目名称]")
        sqlcmd.Parameters.Add("p3", SqlDbType.NVarChar, 100, "[摘要]")
        sqlcmd.Parameters.Add("p4", SqlDbType.NVarChar, 100, "[主体帐簿名称]")
        sqlcmd.Parameters.Add("p5", SqlDbType.NVarChar, 100, "[部门档案名称]")
        sqlcmd.Parameters.Add("p6", SqlDbType.NVarChar, 100, "[收支类型辅助核算名称]")
        sqlcmd.Parameters.Add("p7", SqlDbType.NVarChar, 100, "[期初余额方向]")
        sqlcmd.Parameters.Add("p8", SqlDbType.Decimal, 20, "[期初余额]")
        sqlcmd.Parameters.Add("p9", SqlDbType.Decimal, 20, "[本期借方]")
        sqlcmd.Parameters.Add("p10", SqlDbType.Decimal, 20, "[本期贷方]")
        sqlcmd.Parameters.Add("p11", SqlDbType.Decimal, 20, "[借方累计]")
        sqlcmd.Parameters.Add("p12", SqlDbType.Decimal, 20, "[贷方累计]")
        sqlcmd.Parameters.Add("p13", SqlDbType.NVarChar, 100, "[期末余额方向]")
        sqlcmd.Parameters.Add("p14", SqlDbType.Decimal, 20, "[期末余额]")
        sqlcmd.Parameters.Add("p15", SqlDbType.NVarChar, 100, "[数据日期]")
        sqlcmd.Parameters.Add("p16", SqlDbType.NVarChar, 100, "[导入日期]")

        '600104/主营业务收入/代理业务收入 需要 “收支类型辅助核算名称” 列
        Dim 有辅助核算名称 As Boolean = False
        Dim 辅助核算名称 As String = ""
        Dim row_offset As Integer = 0
        If kmbm = "600104" Then
            If sht.GetRow(6).Cells(3).StringCellValue <> "收支类型辅助核算名称" Then
                msg_stack.Push(msg_stack.Pop + vbCrLf + "导入失败，[600104/主营业务收入/代理业务收入]表没有""收支类型辅助核算名称""列")
                Return False
            Else
                有辅助核算名称 = True
                row_offset = 1
            End If
        End If
        For i = 8 To sht.LastRowNum
            Dim row As NPOI.HSSF.UserModel.HSSFRow = sht.GetRow(i)
            sqlcmd.Parameters(0).Value = kmbm
            sqlcmd.Parameters(1).Value = kmmc
            sqlcmd.Parameters(2).Value = row.Cells(0).StringCellValue
            sqlcmd.Parameters(3).Value = row.Cells(1).StringCellValue
            sqlcmd.Parameters(4).Value = row.Cells(2).StringCellValue
            If 有辅助核算名称 Then
                辅助核算名称 = row.Cells(3).StringCellValue
            Else
                辅助核算名称 = "不需要"
            End If
            sqlcmd.Parameters(5).Value = 辅助核算名称
            sqlcmd.Parameters(6).Value = row.Cells(3 + row_offset).StringCellValue
            sqlcmd.Parameters(7).Value = convert_num(row.Cells(4 + row_offset))
            sqlcmd.Parameters(8).Value = convert_num(row.Cells(5 + row_offset))
            sqlcmd.Parameters(9).Value = convert_num(row.Cells(6 + row_offset))
            sqlcmd.Parameters(10).Value = convert_num(row.Cells(7 + row_offset))
            sqlcmd.Parameters(11).Value = convert_num(row.Cells(8 + row_offset))
            sqlcmd.Parameters(12).Value = row.Cells(9 + row_offset).StringCellValue
            sqlcmd.Parameters(13).Value = convert_num(row.Cells(10 + row_offset))
            sqlcmd.Parameters(14).Value = dt
            sqlcmd.Parameters(15).Value = ""
            sqlcmd.ExecuteNonQuery()

            If row.Cells(0).StringCellValue = "总计" Then
                Exit For
            End If
        Next

        msg_stack.Pop()
        Return True

    End Function
    Function get_科目余额表(sht As NPOI.HSSF.UserModel.HSSFSheet) As Boolean
        msg_stack.Push(sht.SheetName)
        Try

            Dim dt As String = sht.GetRow(3).GetCell(1).StringCellValue

            Dim sqlcmd As New SqlServerCe.SqlCeCommand
            sqlcmd.Connection = data_cnn

            sqlcmd.CommandText = "delete from 科目余额表 where 数据日期='" + dt + "'"
            'sqlcmd.ExecuteNonQuery()
            'transaction.Commit()
            'transaction = SQLCEconn.BeginTransaction(IsolationLevel.ReadCommitted)
            sqlcmd.CommandText = "INSERT INTO [科目余额表] ( [科目编码], [科目名称], [主体帐簿名称], [期初余额方向], [期初余额], [本期借方], [本期贷方], [借方累计], [贷方累计], [期末余额方向], [期末余额],[数据日期],[导入日期]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12,@p13)"

            sqlcmd.Parameters.Clear()
            sqlcmd.Parameters.Add("p1", SqlDbType.NVarChar, 100, "[科目编码]")
            sqlcmd.Parameters.Add("p2", SqlDbType.NVarChar, 200, "[科目名称]")
            sqlcmd.Parameters.Add("p3", SqlDbType.NVarChar, 100, "[主体帐簿名称]")
            sqlcmd.Parameters.Add("p4", SqlDbType.NVarChar, 100, "[期初余额方向]")
            sqlcmd.Parameters.Add("p5", SqlDbType.Decimal, 20, "[期初余额]")
            sqlcmd.Parameters.Add("p6", SqlDbType.Decimal, 20, "[本期借方]")
            sqlcmd.Parameters.Add("p7", SqlDbType.Decimal, 20, "[本期贷方]")
            sqlcmd.Parameters.Add("p8", SqlDbType.Decimal, 20, "[借方累计]")
            sqlcmd.Parameters.Add("p9", SqlDbType.Decimal, 20, "[贷方累计]")
            sqlcmd.Parameters.Add("p10", SqlDbType.NVarChar, 100, "[期末余额方向]")
            sqlcmd.Parameters.Add("p11", SqlDbType.Decimal, 20, "[期末余额]")
            sqlcmd.Parameters.Add("p12", SqlDbType.NVarChar, 100, "[数据日期]")
            sqlcmd.Parameters.Add("p13", SqlDbType.NVarChar, 100, "[导入日期]")

            For i = 7 To sht.LastRowNum
                Dim row As NPOI.HSSF.UserModel.HSSFRow = sht.GetRow(i)
                sqlcmd.Parameters(0).Value = row.Cells(0).StringCellValue
                sqlcmd.Parameters(1).Value = row.Cells(1).StringCellValue
                sqlcmd.Parameters(2).Value = row.Cells(2).StringCellValue
                sqlcmd.Parameters(3).Value = row.Cells(3).StringCellValue
                sqlcmd.Parameters(4).Value = convert_num(row.Cells(4))
                sqlcmd.Parameters(5).Value = convert_num(row.Cells(5))
                sqlcmd.Parameters(6).Value = convert_num(row.Cells(6))
                sqlcmd.Parameters(7).Value = convert_num(row.Cells(7))
                sqlcmd.Parameters(8).Value = convert_num(row.Cells(8))
                sqlcmd.Parameters(9).Value = row.Cells(9).StringCellValue
                sqlcmd.Parameters(10).Value = convert_num(row.Cells(10))
                sqlcmd.Parameters(11).Value = dt
                sqlcmd.Parameters(12).Value = ""
                sqlcmd.ExecuteNonQuery()

                If row.Cells(0).StringCellValue = "总计" Then
                    Exit For
                End If
            Next

        Catch ex As Exception
            msg_stack.Push(msg_stack.Pop + vbCrLf + ex.Message)
            Return False
        End Try

        msg_stack.Pop()
        Return True
    End Function
    Private Function convert_num(cell As NPOI.HSSF.UserModel.HSSFCell) As Decimal
        If cell.CellType = NPOI.SS.UserModel.CellType.NUMERIC Then
            Return cell.NumericCellValue
        Else
            If cell.StringCellValue = "" Then
                Return 0
            Else
                Return Convert.ToDecimal(cell.StringCellValue.Replace(",", ""))
            End If

        End If
    End Function

    Private Function process_dt(instr As String, yyyymm As String) As Boolean
        msg_stack.Push("比对日期失败!" + vbCrLf)
        Dim match As System.Text.RegularExpressions.Match = System.Text.RegularExpressions.Regex.Match(instr, "期间：(\d\d\d\d).(\d\d)-(\d\d\d\d).(\d\d)")
        If match.Success = True Then
            Dim yyyymm1 As String = match.Groups(1).Value + match.Groups(2).Value
            Dim yyyymm2 As String = match.Groups(3).Value + match.Groups(4).Value

            If yyyymm1 = yyyymm2 Then
                If yyyymm1 = yyyymm Then
                Else

                    msg_stack.Push(msg_stack.Pop() + (vbCrLf + "内部表日期：" + yyyymm1 + "与文件名称日期" + yyyymm + "不一致"))
                    Return False
                End If
            Else
                msg_stack.Push(msg_stack.Pop() + (vbCrLf + "开始月" + yyyymm1 + "与结束月" + yyyymm1 + "必须一致"))
                Return False

            End If
        Else
            msg_stack.Push(msg_stack.Pop() + (vbCrLf + "内部表""期间""内容：" + instr + "   匹配失败， 只接受 期间：年年年年.月月-年年年年.月月"))
            Return False
        End If
        msg_stack.Pop()
        Return True
    End Function
    Public Sub send_msg(stack As System.Collections.Generic.Stack(Of String))
        Dim msg As String = ""
        While stack.Count > 0
            msg = stack.Pop + vbCrLf + msg
        End While
        MsgBox(msg)
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Dim 数据字典表_fi As New IO.FileInfo(IO.Path.Combine(data_path, "数据字典表imp.xls"))
        CXls.xls2sqlce(data_cnn, 数据字典表_fi)

    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim sql As String
        Dim ds As DataSet
        Dim outstr As String

        

        Dim book As New NPOI.HSSF.UserModel.HSSFWorkbook()
        Dim sheet As NPOI.SS.UserModel.ISheet


        sheet = book.CreateSheet("管理费用")
        sql = IO.File.ReadAllText(IO.Path.Combine(sql_path, "管理费_out_管理表.sql"), System.Text.Encoding.Default)
        ds = CXls.sqlceScript(sql, data_cnn)
        outstr = CXls.dataset2out(ds, False, True, True, sheet)

        sheet = book.CreateSheet("管理费+主营业务成本")
        sql = IO.File.ReadAllText(IO.Path.Combine(sql_path, "管理费_out_管理&成本.sql"), System.Text.Encoding.Default)
        ds = CXls.sqlceScript(sql, data_cnn)
        outstr = CXls.dataset2out(ds, False, True, True, sheet)

        sheet = book.CreateSheet("收入汇总")
        sql = IO.File.ReadAllText(IO.Path.Combine(sql_path, "收入表_out_收入.sql"), System.Text.Encoding.Default)
        ds = CXls.sqlceScript(sql, data_cnn)
        outstr = CXls.dataset2out(ds, False, True, True, sheet)

        sheet = book.CreateSheet("直接成本统计")
        sql = IO.File.ReadAllText(IO.Path.Combine(sql_path, "收入表_out_成本.sql"), System.Text.Encoding.Default)
        ds = CXls.sqlceScript(sql, data_cnn)
        outstr = CXls.dataset2out(ds, False, True, True, sheet)



        Dim xls_fi As New IO.FileInfo(IO.Path.Combine(data_path, "输出.xls"))
        While 1 = 1
            Try
                If xls_fi.Exists Then xls_fi.Delete()
                Exit While
            Catch ex As System.IO.IOException
                If MsgBox(ex.Message + "，关闭打开的 输出.xls 点重试，或点取消生成", MsgBoxStyle.RetryCancel) <> MsgBoxResult.Retry Then
                    Exit Sub
                End If
            End Try

        End While
        Dim fs As IO.FileStream = xls_fi.Create()
        book.Write(fs)
        book = Nothing
        fs.Close()
        System.Diagnostics.Process.Start(xls_fi.FullName)

    End Sub
End Class
